package ec.edu.ups.cidi.siia.plamdep.analizadorLexico.wordnet;

import java.sql.*;
import ec.edu.ups.cidi.siia.plamdep.configuracion.ConfiguracionGlobal;
import ec.edu.ups.cidi.siia.plamdep.interfaces.IConexionBD;
import ec.edu.ups.cidi.siia.plamdep.utils.Log;
/**
 * Clase que permite interactuar con la BD wordnet en HSQL
 * @author user
 *
 */
public class ConexionBDHSQL implements IConexionBD{
    
    static Connection con  = null;
    static Statement  st   = null;  
    static ResultSet  res  = null; 
    static String     sql  = null;
    static boolean conectado=false;
    private  final String DBH = "jdbc:hsqldb:file:"+ConfiguracionGlobal.pathResources+"/bd_wordnet_hsql/prueba";
  
    public ConexionBDHSQL()
    {
        this.conectar();
    }
public void cargarBD(){
    
      
       Log.log(null,Log.MOD_ANLS_LEXICO,Log.LEVEL_LOAD, ConexionBDHSQL.class,"* Iniciando la BD...");
       
        try
        {
            Class.forName("org.hsqldb.jdbcDriver"); //Load HSQLDB driver
            
            Log.log(null,Log.MOD_ANLS_LEXICO,Log.LEVEL_LOAD, ConexionBDHSQL.class,"* Cargando controlador HSQLDB...");
            
        } 
        catch(Exception e)
        {  
        	Log.log(e,Log.MOD_ANLS_LEXICO,Log.LEVEL_ERROR, ConexionBDHSQL.class,"ERROR: failed to load HSQLDB JDBC driver.");
            
            e.printStackTrace();
            return;  
        } 
  }    


 public  void connectDB()
    {
        try
        {   // Connect to the database or create if it don't exist 
            con = DriverManager.getConnection(DBH,"sa","sa"); 
            
            Log.log(null,Log.MOD_ANLS_LEXICO,Log.LEVEL_LOAD, ConexionBDHSQL.class,"* Creando la conexion HSQLDB...");
            
        }
        catch(Exception e)
        {
            
            Log.log(e,Log.MOD_ANLS_LEXICO,Log.LEVEL_ERROR, ConexionBDHSQL.class,"Error connectDB: ");
            return;
        }
    }
 
  
 public  void createStatement()
    {
        try
        {
            st  = con.createStatement(); 
        }
        catch (Exception e)
        {  
             
            Log.log(e,Log.MOD_ANLS_LEXICO,Log.LEVEL_ERROR, ConexionBDHSQL.class,"Error: createStatement: ");
            return;
        }
    }
 
 
 public  void closeHSQLDB()
    {
        
        try
        {
            st = con.createStatement();  
            st.executeUpdate("SHUTDOWN");  
            st.close(); 
        }
        catch(Exception e)
        {
          
            Log.log(e,Log.MOD_ANLS_LEXICO,Log.LEVEL_ERROR, ConexionBDHSQL.class,"Error: save temporal data");
        }
 
        try
        {
            con.close();
        }
        catch(Exception e)
        {
           
            Log.log(e,Log.MOD_ANLS_LEXICO,Log.LEVEL_ERROR, ConexionBDHSQL.class,"Error closeHSQLDB: ");
            e.printStackTrace();
            return; 
        }
    }
        

  public ResultSet consulta(String query)
    {
        ResultSet rs=null;
        try 
        {   Statement sentencia = con.createStatement();
            rs=sentencia.executeQuery(query);
        } catch (SQLException e) { 
           
            
            Log.log(e,Log.MOD_ANLS_LEXICO,Log.LEVEL_ERROR, ConexionBDHSQL.class,"Error consulta: ");

        }
        return rs;
    }
 
 public void ejecutar(String codigo){
       
      
            try{
            PreparedStatement pstm = con.prepareStatement(codigo);
            pstm.execute();
             
            pstm.close();
            }catch(Exception e){ 
            Log.log(e,Log.MOD_ANLS_LEXICO,Log.LEVEL_ERROR, ConexionBDHSQL.class," error ejecutar: ");

            } 
   }

    
    public void conectar() 
    {
        this.cargarBD();
        this.connectDB();
        conectado=true;
		Log.log(null, Log.MOD_ANLS_LEXICO, Log.LEVEL_SUCESS, ConexionBDHSQL.class, "Exito al conectar HSQL");
    }

    
    public void cerrar() {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    
    public boolean isConectado() {
 
    	return conectado;
    }
	public String getSqlSinonimos(String palabra, String lema) {
  	  String sql="select distinct word " +
              " from palabra v1, " +
              " significados s " +
              " where v1.offset in  "+
              
              "(select v2.offset "+
              "	from palabra v2 "+
              "	where upper(v2.word)= upper('"+palabra+"') or "+
              " upper(v2.word)= upper('"+lema+"') ) " +
              
              " and s.offset=v1.offset";
	   sql=sql.replace("\n"," ");

		return sql;
	}
	public String getSqlAntonimos(String palabra) {
		String sql=" select distinct v2.word p2 "
				+ " from "
				+ " 	(select * from relaciones   where relation=33)r,"
				+ "		(select * from palabra where upper(word)=upper('"+palabra+"'))v1,"
				+ "		 palabra v2 "
				+ " where"
				+ "		v1.offset=r.\"sourceSynset\" and"
				+ "		v2.offset=r.\"targetSynset\"";
		return sql;
	}
	public String getSqlSignificados(String palabra) {
		String sql="select s.GLOSS\n" +
                " from significados s, palabra p\n" +
                " where \n" +
                "p.WORD like '"+palabra+"'\n" +
                "and s.GLOSS <> 'NULL'\n" +
                "and  s.OFFSET like p.OFFSET  ";
		return sql;
	}
	public String getSqlTraducciones(String palabra) {
		String sql=" select ing.word, ing.pos\n" +
                "from(\n" +
                "select es1.offset\n" +
                "from  palabra as es1\n" +
                "where es1.word like '"+palabra.toLowerCase()+"') as es,\n" +
                "palabra_eng as ing\n" +
                "where substring(es.offset,5,13) = substring(ing.offset,5,13);";
		return sql;
	}
  
}